package com.samsung.dementia.database;

public class Query {

	public static final String TABLE_NAME_USER_ACCOUNT = "user_account";
	public static final String TABLE_NAME_DQ_APP_DATA = "dq_app_data";
	public static final String TABLE_NAME_SOCIAL_DATA = "social_data";
	public static final String TABLE_NAME_HMR_DATA = "hmr_data";
	public static final String TABLE_NAME_LOCATION_DATA = "location_data";
	public static final String TABLE_NAME_COGNITION_DATA = "cognition_data";
	public static final String TABLE_NAME_VOICE_DATA = "voice_data";
	public static final String TABLE_NAME_PHOTO_DATA = "face_photo_data";
	
	public static final String[] ALL_TABLE_NAMES = {TABLE_NAME_USER_ACCOUNT, TABLE_NAME_DQ_APP_DATA, TABLE_NAME_SOCIAL_DATA, 
		TABLE_NAME_HMR_DATA, TABLE_NAME_LOCATION_DATA, TABLE_NAME_COGNITION_DATA, TABLE_NAME_VOICE_DATA, TABLE_NAME_PHOTO_DATA};
	
	/* CREATE QUERY */	
	public static final String QUERY_CREATE_TABLE_USER_ACCOUNT = "CREATE TABLE " + TABLE_NAME_USER_ACCOUNT + "(" +
			"id VARCHAR(30) NOT NULL, " +
			"pwd VARCHAR(30) NOT NULL, " +
			"timestamp VARCHAR(30) NOT NULL, " +
			"access_time VARCHAR(30) NULL, " +
			"model_name VARCHAR(40) NULL, " +
			"android_version VARCHAR(30) NULL, " +
			"PRIMARY KEY (`id`)" +
			")";
		
	public static final String QUERY_CREATE_TABLE_DQ_APP_DATA = "CREATE TABLE " + TABLE_NAME_DQ_APP_DATA + "(" +
			"id VARCHAR(30) NOT NULL, " +
			"timestamp VARCHAR(30) NOT NULL, " +
			"year VARCHAR(10) NOT NULL, " +
			"month VARCHAR(10) NOT NULL, " +
			"dayOfMonth VARCHAR(10) NOT NULL, " +
			"hour VARCHAR(10) NOT NULL, " +
			"minute VARCHAR(10) NOT NULL, " +
			"second VARCHAR(10) NOT NULL, " +
			"data TEXT NOT NULL" +
			")";
	
	public static final String QUERY_CREATE_TABLE_SOCIAL_DATA = "CREATE TABLE " + TABLE_NAME_SOCIAL_DATA + "(" +
			"id VARCHAR(30) NOT NULL, " +
			"timestamp VARCHAR(30) NOT NULL, " +
			"year VARCHAR(10) NOT NULL, " +
			"month VARCHAR(10) NOT NULL, " +
			"dayOfMonth VARCHAR(10) NOT NULL, " +
			"hour VARCHAR(10) NOT NULL, " +
			"minute VARCHAR(10) NOT NULL, " +
			"second VARCHAR(10) NOT NULL, " +
			"data MEDIUMTEXT NOT NULL" +
			")";
	
	public static final String QUERY_CREATE_TABLE_HMR_DATA = "CREATE TABLE " + TABLE_NAME_HMR_DATA + "(" +
			"id VARCHAR(30) NOT NULL, " +
			"timestamp VARCHAR(30) NOT NULL, " +
			"year VARCHAR(10) NOT NULL, " +
			"month VARCHAR(10) NOT NULL, " +
			"dayOfMonth VARCHAR(10) NOT NULL, " +
			"hour VARCHAR(10) NOT NULL, " +
			"minute VARCHAR(10) NOT NULL, " +
			"second VARCHAR(10) NOT NULL, " +
			"data MEDIUMTEXT NOT NULL" +
			")";
	
	public static final String QUERY_CREATE_TABLE_LOCATION_DATA = "CREATE TABLE " + TABLE_NAME_LOCATION_DATA + "(" +
			"id VARCHAR(30) NOT NULL, " +
			"timestamp VARCHAR(30) NOT NULL, " +
			"year VARCHAR(10) NOT NULL, " +
			"month VARCHAR(10) NOT NULL, " +
			"dayOfMonth VARCHAR(10) NOT NULL, " +
			"hour VARCHAR(10) NOT NULL, " +
			"minute VARCHAR(10) NOT NULL, " +
			"second VARCHAR(10) NOT NULL, " +
			"data MEDIUMTEXT NOT NULL" +
			")";
	
	public static final String QUERY_CREATE_TABLE_COGNITION_DATA = "CREATE  TABLE " + TABLE_NAME_COGNITION_DATA + "(" +
			"id VARCHAR(30) NOT NULL, " +
			"timestamp VARCHAR(30) NOT NULL, " +
			"year VARCHAR(10) NOT NULL, " +
			"month VARCHAR(10) NOT NULL, " +
			"dayOfMonth VARCHAR(10) NOT NULL, " +
			"hour VARCHAR(10) NOT NULL, " +
			"minute VARCHAR(10) NOT NULL, " +
			"second VARCHAR(10) NOT NULL, " +
			"data MEDIUMTEXT NOT NULL" +
			")";
	
	public static final String QUERY_CREATE_TABLE_VOICE_DATA = "CREATE  TABLE " + TABLE_NAME_VOICE_DATA + "(" +
			"id VARCHAR(30) NOT NULL ," +
			"timestamp VARCHAR(30) NOT NULL, " +
			"data MEDIUMBLOB NOT NULL" +
			")";
	
	public static final String QUERY_CREATE_TABLE_PHOTO_DATA = "CREATE  TABLE " + TABLE_NAME_PHOTO_DATA + "(" +
			"id VARCHAR(30) NOT NULL ," +
			"timestamp VARCHAR(30) NOT NULL, " +
			"data MEDIUMBLOB NOT NULL" +
			")";
	
	public static final String[] QUERY_CREATE_TALBLES = {QUERY_CREATE_TABLE_USER_ACCOUNT, 
		QUERY_CREATE_TABLE_DQ_APP_DATA, QUERY_CREATE_TABLE_COGNITION_DATA, QUERY_CREATE_TABLE_LOCATION_DATA, 
		QUERY_CREATE_TABLE_PHOTO_DATA, QUERY_CREATE_TABLE_HMR_DATA, QUERY_CREATE_TABLE_SOCIAL_DATA, 
		QUERY_CREATE_TABLE_VOICE_DATA};
	
	
	/* INSERT QUERY */
	public static String QUERY_INSERT_USER_ACCOUNT ="INSERT INTO " + TABLE_NAME_USER_ACCOUNT  +
			"(id, pwd, timestamp) " +
			"VALUES" +
			"('v_id', 'v_pwd', 'v_timestamp')";
	
	public static String QUERY_INSERT_DQ_DATA = "INSERT INTO " + TABLE_NAME_DQ_APP_DATA  +
			"(id, data, timestamp, year, month, dayOfMonth, hour, minute, second) " +
			"VALUES" +
			"('v_id', 'v_data', 'v_timestamp', 'v_year', 'v_month', 'v_dayOfMonth', 'v_hour', 'v_minute', 'v_second')";
	
	public static String QUERY_INSERT_LOCATION_DATA = "INSERT INTO " + TABLE_NAME_LOCATION_DATA  +
			"(id, data, timestamp, year, month, dayOfMonth, hour, minute, second) " +
			"VALUES" +
			"('v_id', 'v_data', 'v_timestamp', 'v_year', 'v_month', 'v_dayOfMonth', 'v_hour', 'v_minute', 'v_second')";
	
	public static String QUERY_INSERT_HMR_DATA = "INSERT INTO " + TABLE_NAME_HMR_DATA  +
			"(id, data, timestamp, year, month, dayOfMonth, hour, minute, second) " +
			"VALUES" +
			"('v_id', 'v_data', 'v_timestamp', 'v_year', 'v_month', 'v_dayOfMonth', 'v_hour', 'v_minute', 'v_second')";
	
	public static String QUERY_INSERT_APP_DATA = "INSERT INTO " + TABLE_NAME_COGNITION_DATA  +
			"(id, data, timestamp, year, month, dayOfMonth, hour, minute, second) " +
			"VALUES" +
			"('v_id', 'v_data', 'v_timestamp', 'v_year', 'v_month', 'v_dayOfMonth', 'v_hour', 'v_minute', 'v_second')";
	
	public static String QUERY_INSERT_SOCIAL_DATA = "INSERT INTO " + TABLE_NAME_SOCIAL_DATA  +
			"(id, data, timestamp, year, month, dayOfMonth, hour, minute, second) " +
			"VALUES" +
			"('v_id', 'v_data', 'v_timestamp', 'v_year', 'v_month', 'v_dayOfMonth', 'v_hour', 'v_minute', 'v_second')";
	
	
	
	/* SELECT QUERY */
	public static String QUERY_SELECT_LOGIN_INFO = "SELECT * FROM " + TABLE_NAME_USER_ACCOUNT + 
			" WHERE id='v_id' AND pwd='v_pwd'";
	
	
	
		
	/* UPDATE QUERY */
	public static String QUERY_UPDATE_TIMESTAMP_FOR_LOGIN = "UPDATE " + TABLE_NAME_USER_ACCOUNT + 
			" SET timestamp='v_timestamp', model_name='v_model_name', android_version='v_android_version' where id='v_id' and pwd='v_pwd'";
	
	public static String QUERY_UPDATE_USER_ACCESS_TIME = "UPDATE " + TABLE_NAME_USER_ACCOUNT + 
			" SET access_time='v_access_time' where id='v_id'";
	
	
	
	/* DROP QUERY */
	public static final String QUERY_DROP_TABLE_QUERY = "drop table if exsits v_tablename";

}
